import tkinter as tk
from tkinter import filedialog, messagebox
import pandas as pd
import ipaddress

class ExcelSearchApp:
    def __init__(self, root):
        """初始化应用程序"""
        self.root = root
        self.root.title("Excel Search App")

        # 创建控制变量
        self.file_path = tk.StringVar()       # 存储选择的文件路径
        self.query_text = tk.StringVar()      # 存储查询文本
        self.sheet_name = tk.StringVar()      # 存储工作表名称
        self.column1 = tk.StringVar()         # 存储第一个查询列名
        self.column2 = tk.StringVar()         # 存储第二个查询列名
        self.column3 = tk.StringVar()         # 存储第三个查询列名
        
        # 新增：存储工作表名称和列名列表
        self.sheet_names = []
        self.column_names = []

        # 创建GUI界面
        self.create_widgets()

    def create_widgets(self):
        """创建图形界面组件"""
        # 文件选择区域
        tk.Label(self.root, text="文件路径:").grid(row=0, column=0)
        tk.Entry(self.root, textvariable=self.file_path, state='readonly', width=100).grid(row=0, column=1)
        tk.Button(self.root, text="选择文件", command=self.select_file).grid(row=0, column=2)

        tk.Label(self.root, text="工作表名称:").grid(row=1, column=0)
        self.sheet_menu = tk.OptionMenu(self.root, self.sheet_name, "")
        self.sheet_menu.config(width=97)  # OptionMenu需要减3以匹配Entry宽度
        self.sheet_menu.grid(row=1, column=1)

        tk.Label(self.root, text="查询列1:").grid(row=2, column=0)
        self.column1_menu = tk.OptionMenu(self.root, self.column1, "")
        self.column1_menu.config(width=97)
        self.column1_menu.grid(row=2, column=1)

        tk.Label(self.root, text="查询列2:").grid(row=3, column=0)
        self.column2_menu = tk.OptionMenu(self.root, self.column2, "")
        self.column2_menu.config(width=97)
        self.column2_menu.grid(row=3, column=1)

        tk.Label(self.root, text="查询列3:").grid(row=4, column=0)
        self.column3_menu = tk.OptionMenu(self.root, self.column3, "")
        self.column3_menu.config(width=97)
        self.column3_menu.grid(row=4, column=1)

        # 功能按钮区域
        button_frame = tk.Frame(self.root)
        button_frame.grid(row=5, column=0, columnspan=2, sticky='w')
        tk.Button(button_frame, text="模糊查询", command=self.fuzzy_search).pack(side='left', padx=5)
        tk.Button(button_frame, text="IP段查询", command=self.ip_range_search).pack(side='left', padx=5)

        # 查询条件区域
        tk.Label(self.root, text="查询内容:").grid(row=6, column=0)
        self.query_text = tk.Text(self.root, width=100, height=50)
        self.query_text.grid(row=6, column=1)

        # 结果显示区域
        self.result_text = tk.Text(self.root, width=100, height=50)
        self.result_text.grid(row=6, column=3, columnspan=3)

    def select_file(self):
        """选择文件并读取文件信息"""
        file_path = filedialog.askopenfilename()
        if file_path:
            self.file_path.set(file_path)

            if  file_path.endswith(('.xls', '.xlsx')):
                # 如果是普通Excel文件，直接读取
                self.read_sheet_names_and_columns(file_path)
            else:
                messagebox.showinfo("错误", "未知的文件类型")

    def read_sheet_names_and_columns(self, file_path):
        """读取Excel文件的工作表名称和列名"""
        try:
            xls = pd.ExcelFile(file_path)
            self.sheet_names = xls.sheet_names
            df = xls.parse(self.sheet_names[0])  # 解析第一个工作表
            self.column_names = df.columns.tolist()
            
            # 更新工作表下拉列表
            self.sheet_menu['menu'].delete(0, 'end')
            for name in self.sheet_names:
                self.sheet_menu['menu'].add_command(label=name, command=tk._setit(self.sheet_name, name))
            
            # 更新列下拉列表
            for menu, var in [(self.column1_menu, self.column1), 
                             (self.column2_menu, self.column2), 
                             (self.column3_menu, self.column3)]:
                menu['menu'].delete(0, 'end')
                for col in self.column_names:
                    menu['menu'].add_command(label=col, command=tk._setit(var, col))
            
        except Exception as e:
            messagebox.showinfo("错误", f"读取Excel表结构时发生错误：{e}")

    def fuzzy_search(self):
        """执行模糊查询"""
        # 获取查询参数
        file_path = self.file_path.get()
        query = self.query_text.get(1.0, tk.END).strip()  # 修改此行
        sheet = self.sheet_name.get()
        col1 = self.column1.get()
        col2 = self.column2.get()
        col3 = self.column3.get()

        # 验证查询参数
        if not all([file_path, query, sheet, col1, col2, col3]):
            messagebox.showinfo("错误", "请选择文件、工作表和查询列")
            return

        # 获取实际文件路径
        actual_path = file_path

        try:
            # 读取Excel文件
            xls = pd.ExcelFile(actual_path)
            df = xls.parse(sheet)
            keywords = query.split('\n')  # 支持多行关键词
            result = pd.DataFrame()
            
            # 对每个关键词进行模糊查询
            for keyword in keywords:
                temp_result = df[df[col1].str.contains(keyword, na=False) |
                                 df[col2].str.contains(keyword, na=False) |
                                 df[col3].str.contains(keyword, na=False)]
                result = pd.concat([result, temp_result])

            # 显示查询结果
            if not result.empty:
                self.result_text.delete(1.0, tk.END)  # 清空结果区域
                for index, row in result.iterrows():
                    for col in df.columns:
                        self.result_text.insert(tk.END, f"{row[col]}     ")
                    self.result_text.insert(tk.END, '\n\n')
            else:
                self.result_text.delete(1.0, tk.END)
                self.result_text.insert(tk.END, "未找到匹配的数据。")
        except Exception as e:
            self.result_text.delete(1.0, tk.END)
            self.result_text.insert(tk.END, f"读取Excel时发生错误：{e}")

    def ip_range_search(self):
        """执行IP段查询"""
        # 获取查询参数
        file_path = self.file_path.get()
        query = self.query_text.get(1.0, tk.END).strip()  # 修改此行
        sheet = self.sheet_name.get()
        col1 = self.column1.get()
        col2 = self.column2.get()
        col3 = self.column3.get()

        # 验证查询参数
        if not all([file_path, query, sheet, col1, col2, col3]):
            messagebox.showinfo("错误", "请选择文件、工作表和查询列")
            return

        # 获取实际文件路径（处理加密文件）
        actual_path = file_path

        try:
            # 读取Excel文件
            xls = pd.ExcelFile(actual_path)
            df = xls.parse(sheet)
            ips = query.split('\n')  # 支持多行IP地址
            result = pd.DataFrame()
            
            # 对每个IP地址进行范围查询
            import re
            ip_pattern = re.compile(r'^(?:[0-9]{1,3}\.){3}[0-9]{1,3}$')
            
            for ip in ips:
                # 1. 移除所有非数字和点的字符
                cleaned_ip = re.sub(r'[^0-9.]', '', ip)
                # 2. 验证IP格式
                if not ip_pattern.match(cleaned_ip):
                    messagebox.showinfo("错误", f"无效的IP地址格式: {ip} (清洗后: {cleaned_ip})")
                    continue
                # 3. 验证每个段的范围
                octets = list(map(int, cleaned_ip.split('.')))
                if len(octets) != 4 or any(o > 255 for o in octets):
                    messagebox.showinfo("错误", f"IP地址超出范围: {cleaned_ip}")
                    continue
                
                try:
                    ip_obj = ipaddress.ip_address(cleaned_ip)
                    for index, row in df.iterrows():
                        # 处理Excel中的IP地址
                        start_ip_str = str(row[col1]).strip()
                        end_ip_str = str(row[col2]).strip()
                        
                        # 新增：处理IP地址中的前导零
                        def clean_ip_octets(ip_str):
                            # 移除非数字和点的字符
                            cleaned = re.sub(r'[^0-9.]', '', ip_str)
                            octets = cleaned.split('.')
                            if len(octets) != 4:
                                return None
                            # 处理每个段的前导零
                            cleaned_octets = []
                            for octet in octets:
                                if not octet:  # 跳过空段
                                    return None
                                # 移除前导零，但保留单个零
                                if len(octet) > 1 and octet.startswith('0'):
                                    octet = octet.lstrip('0')
                                    if not octet:  # 如果全是零，保留一个零
                                        octet = '0'
                                cleaned_octets.append(octet)
                            return '.'.join(cleaned_octets)
                        
                        # 清洗Excel中的IP
                        start_ip_clean = clean_ip_octets(start_ip_str)
                        end_ip_clean = clean_ip_octets(end_ip_str)
                        
                        if not start_ip_clean or not end_ip_clean:
                            messagebox.showinfo("错误", f"无效的IP格式: {start_ip_str} 或 {end_ip_str}")
                            continue
                        
                        start_ip = ipaddress.ip_address(start_ip_clean)
                        end_ip = ipaddress.ip_address(end_ip_clean)
                        if start_ip <= ip_obj <= end_ip:
                            result = pd.concat([result, df.loc[[index]]])
                except ValueError as e:
                    messagebox.showinfo("错误", f"IP解析失败: {cleaned_ip}, 原因: {str(e)}")

            # 显示查询结果
            if not result.empty:
                self.result_text.delete(1.0, tk.END)  # 清空结果区域
                for index, row in result.iterrows():
                    for col in df.columns:
                        self.result_text.insert(tk.END, f"{row[col]}     ")
                    self.result_text.insert(tk.END, '\n\n')
            else:
                self.result_text.delete(1.0, tk.END)
                self.result_text.insert(tk.END, "未找到匹配的IP段。")
        except Exception as e:
            self.result_text.delete(1.0, tk.END)
            self.result_text.insert(tk.END, f"读取Excel时发生错误：{e}")

if __name__ == "__main__":
    """程序入口点"""
    root = tk.Tk()
    app = ExcelSearchApp(root)
    root.mainloop()